﻿-- Cập Nhật Mật Khẩu
IF OBJECT_ID('sp_CapNhatMatKhau', 'P') IS NOT NULL
	DROP PROCEDURE sp_CapNhatMatKhau
GO
CREATE PROCEDURE sp_CapNhatMatKhau (@Userid NVARCHAR(50),@password Nvarchar(50))
AS
BEGIN
	UPDATE NGUOIDUNG
	SET PASSWORD = @password
	WHERE USERID = @Userid
END
GO
-- Thông Tin Tài Khoản Người Dùng
IF OBJECT_ID('sp_ThongTinNguoiDung', 'P') IS NOT NULL
	DROP PROCEDURE sp_ThongTinNguoiDung
GO
CREATE PROCEDURE sp_ThongTinNguoiDung (@Username NVARCHAR(50))
AS
BEGIN
	SELECT * FROM NGUOIDUNG WHERE USERNAME = @Username
END
GO
--Tra Cứu Tài Khoản Người Dùng
IF OBJECT_ID('sp_TraCuuTaiKhoan', 'P') IS NOT NULL
	DROP PROCEDURE sp_TraCuuTaiKhoan
GO
CREATE PROCEDURE sp_TraCuuTaiKhoan (@Username NVARCHAR(50), @Vaitro NVARCHAR(50), @TrangThai bit)
AS
BEGIN
	CREATE TABLE #TAM (USERID INT,USERNAME NVARCHAR(50), VAITRO NVARCHAR(50), TRANGTHAI bit)
	INSERT INTO #TAM (USERID,USERNAME,VAITRO,TRANGTHAI)
	SELECT USERID,USERNAME,VAITRO,TRANGTHAI
	FROM NGUOIDUNG
	WHERE USERNAME LIKE '%' + @Username + '%' 
		 AND VAITRO LIKE N'%' + @Vaitro + '%' 
		 AND TRANGTHAI LIKE @TrangThai  
	SELECT * FROM #TAM
END
GO
-- Thêm mới tài khoản người dùng
IF OBJECT_ID('sp_ThemTaiKhoan', 'P') IS NOT NULL
	DROP PROCEDURE sp_ThemTaiKhoan
GO
CREATE PROCEDURE sp_ThemTaiKhoan (@Username NVARCHAR(50), @Vaitro NVARCHAR(50), @TrangThai bit)
AS
BEGIN
	IF NOT EXISTS (SELECT * FROM NGUOIDUNG WHERE USERNAME = @Username)
		INSERT INTO NGUOIDUNG (USERNAME,PASSWORD,VAITRO,TRANGTHAI) VALUES (@Username,'e10adc3949ba59abbe56e057f20f883e',@Vaitro,@TrangThai)
	ELSE
		RAISERROR  (N'USERNAME ĐÃ TỒN TẠI',16,1)
END
GO

-- Cập nhật tài khoản người dùng
IF OBJECT_ID('sp_CapNhatTaiKhoan', 'P') IS NOT NULL
	DROP PROCEDURE sp_CapNhatTaiKhoan9
GO
CREATE PROCEDURE sp_CapNhatTaiKhoan (@Userid int, @Username NVARCHAR(50), @Vaitro NVARCHAR(50), @TrangThai bit)
AS
BEGIN
	IF NOT EXISTS (SELECT * FROM NGUOIDUNG WHERE USERNAME = @Username)
		IF(@Username != '')
			UPDATE NGUOIDUNG SET USERNAME = @Username, VAITRO = @Vaitro, TRANGTHAI = @TrangThai WHERE USERID = @Userid
		ELSE
			UPDATE NGUOIDUNG SET VAITRO = @Vaitro, TRANGTHAI = @TrangThai WHERE USERID = @Userid	
	ELSE
		RAISERROR  (N'USERNAME ĐÃ TỒN TẠI',16,1)
END
GO

-- Xóa tài khoản người dùng
IF OBJECT_ID('sp_XoaTaiKhoan', 'P') IS NOT NULL
	DROP PROCEDURE sp_XoaTaiKhoan
GO
CREATE PROCEDURE sp_XoaTaiKhoan (@Userid int)
AS
BEGIN
	DELETE FROM NGUOIDUNG WHERE USERID = @Userid
END
GO


--INSERT INTO NGUOIDUNG (USERNAME,PASSWORD,VAITRO,TRANGTHAI) VALUES ('huuhung','e10adc3949ba59abbe56e057f20f883e',N'BAN GIÁM HIỆU',0)
--INSERT INTO NGUOIDUNG (USERNAME,PASSWORD,VAITRO,TRANGTHAI) VALUES ('huuhung1','e10adc3949ba59abbe56e057f20f883e',N'BAN GIÁM HIỆU',1)
--INSERT INTO NGUOIDUNG (USERNAME,PASSWORD,VAITRO,TRANGTHAI) VALUES ('huuhung2','e10adc3949ba59abbe56e057f20f883e',N'GIÁO VỤ',0)
--INSERT INTO NGUOIDUNG (USERNAME,PASSWORD,VAITRO,TRANGTHAI) VALUES ('huuhung3','e10adc3949ba59abbe56e057f20f883e',N'GIÁO VỤ',1)

-- Trigger Mã Tự Tăng Học Sinh

IF OBJECT_ID('fn_NextID', 'P') IS NOT NULL
	DROP PROCEDURE fn_NextID
GO
CREATE FUNCTION fn_NextID (@lastid CHAR(8), @prefix CHAR(2), @size INT)
RETURNS CHAR(8)
AS
BEGIN
	IF (@lastid = '')
		SET @lastid = @prefix + REPLICATE(0, @size - LEN(@prefix))

	DECLARE @num_nextid INT, @nextid CHAR(8)
	SET @lastid = LTRIM(RTRIM(@lastid))
	-- number next id
	SET @num_nextid = replace(@lastid, @prefix, '') + 1
	-- bo di so luong ky tu tien to
	SET @size = @size - len(@prefix)
	-- replicate số lượng số 0 REPLICATE(0,3) => 000
	SET @nextid = @prefix + REPLICATE(0, @size - LEN(@prefix))
	SET @nextid = @prefix + RIGHT(REPLICATE(0, @size) + CONVERT(VARCHAR(MAX), @num_nextid), @size)

	RETURN @nextid
END
GO

-- Trigger Thêm mã Học Sinh
IF OBJECT_ID('tr_NextID_HOCSINH', 'TR') IS NOT NULL
	DROP TRIGGER tr_NextID_HOCSINH
GO
CREATE TRIGGER tr_NextID_HOCSINH ON HOCSINH
FOR INSERT
AS
BEGIN
	DECLARE @lastid CHAR(6)

	SET @lastid = (
			SELECT TOP 1 HOCSINH.MAHS
			FROM HOCSINH
			ORDER BY HOCSINH.MAHS DESC
			)
	UPDATE HocSinh
	SET HOCSINH.MAHS = dbo.fn_NextID(@lastid, 'HS', 6)
	WHERE HOCSINH.MAHS = ''
END
GO

--Liệt Kê Khối Lớp
IF OBJECT_ID('sp_LietKeKhoiLop', 'P') IS NOT NULL
	DROP PROCEDURE sp_LietKeKhoiLop
GO
CREATE PROCEDURE sp_LietKeKhoiLop
AS
BEGIN
	SELECT MAKHOILOP,TENKHOILOP FROM KHOILOP
END
GO

--Liệt kê lớp
IF OBJECT_ID('sp_LietKeLop', 'P') IS NOT NULL
	DROP PROCEDURE sp_LietKeLop
GO
CREATE PROCEDURE sp_LietKeLop
AS
BEGIN
	SELECT MALOP,TENLOP FROM LOP
END

EXEC sp_LietKeLop

--Thêm Học Sinh 
IF OBJECT_ID('sp_ThemHocSinh', 'P') IS NOT NULL
	DROP PROCEDURE sp_ThemHocSinh
GO
CREATE PROCEDURE sp_ThemHocSinh (@HOTEN NVARCHAR(100),@NGAYSINH DATETIME, @DIACHI NVARCHAR(200),@DIENTHOAI CHAR(15),@EMAIL NVARCHAR(50),@GIOITINH BIT)
AS
BEGIN
	INSERT INTO HOCSINH (MAHS,HOTEN,NTNS,DIACHI,DIENTHOAI,EMAIL,GIOITINH) VALUES ('',@HOTEN,@NGAYSINH,@DIACHI,@DIENTHOAI,@EMAIL,@GIOITINH)
END
GO

--Cập nhật thông tin học sinh
IF OBJECT_ID('sp_CapNhatHocSinh', 'P') IS NOT NULL
	DROP PROCEDURE sp_CapNhatHocSinh
GO
CREATE PROCEDURE sp_CapNhatHocSinh (@MAHS CHAR(9),@HOTEN NVARCHAR(100),@NGAYSINH DATETIME, @DIACHI NVARCHAR(200),@DIENTHOAI CHAR(15),@EMAIL NVARCHAR(50),@GIOITINH BIT)
AS
BEGIN
	UPDATE HOCSINH
	SET HOTEN = @HOTEN,NTNS = @NGAYSINH,DIACHI = @DIACHI,DIENTHOAI = @DIENTHOAI, EMAIL = @EMAIL, GIOITINH = @GIOITINH
	WHERE MAHS = @MAHS
END
GO

--Xóa học sinh
IF OBJECT_ID('sp_XoaHocSinh', 'P') IS NOT NULL
	DROP PROCEDURE sp_XoaHocSinh
GO
CREATE PROCEDURE sp_XoaHocSinh (@MAHS CHAR(9))
AS
BEGIN
	DELETE FROM DIEM WHERE MAHS = @MAHS
	DELETE FROM HOCSINH WHERE MAHS = @MAHS
END
GO
-- Liệt kê danh sách thông tin học sinh
IF OBJECT_ID('sp_LietKeThongTinHocSinh', 'P') IS NOT NULL
	DROP PROCEDURE sp_LietKeThongTinHocSinh
GO
CREATE PROCEDURE sp_LietKeThongTinHocSinh 
AS
BEGIN
	CREATE TABLE #TAM (MAHS CHAR(9),HOTEN NVARCHAR(225),NTNS DATETIME, DIACHI NVARCHAR(MAX),DIENTHOAI NVARCHAR(15),EMAIL NVARCHAR(MAX),GIOITINH BIT,TENLOP NVARCHAR(10))
	INSERT INTO #TAM (MAHS,HOTEN,NTNS,DIACHI,DIENTHOAI,EMAIL,GIOITINH,TENLOP)
	SELECT S.MAHS,S.HOTEN,S.NTNS,S.DIACHI,S.DIENTHOAI,S.EMAIL,S.GIOITINH,L.TENLOP FROM HOCSINH S, LOP L WHERE S.MALOP = L.MALOP AND S.MALOP is NOT NULL 
	
	INSERT INTO #TAM (MAHS,HOTEN,NTNS,DIACHI,DIENTHOAI,EMAIL,GIOITINH)
	SELECT S.MAHS,S.HOTEN,S.NTNS,S.DIACHI,S.DIENTHOAI,S.EMAIL,S.GIOITINH FROM HOCSINH S WHERE S.MALOP is NULL 

	SELECT DISTINCT MAHS,HOTEN,NTNS,DIACHI,DIENTHOAI,EMAIL,GIOITINH,TENLOP FROM #TAM ORDER BY HOTEN ASC

END


-- Tra cứu thông tin học sinh
IF OBJECT_ID('sp_TraCuuThongTinHocSinh', 'P') IS NOT NULL
	DROP PROCEDURE sp_TraCuuThongTinHocSinh
GO
CREATE PROCEDURE sp_TraCuuThongTinHocSinh (@MAHS NVARCHAR(9),@HOTEN NVARCHAR(225),@HO NVARCHAR(100),@TEN NVARCHAR(100), @MALOP NVARCHAR(4),@MAKHOILOP NVARCHAR(5),@GIOITINH NVARCHAR(5))
AS
BEGIN
	CREATE TABLE #TAM (MAHS CHAR(9),HOTEN NVARCHAR(225),NTNS DATETIME, DIACHI NVARCHAR(MAX),DIENTHOAI NVARCHAR(15),EMAIL NVARCHAR(MAX),GIOITINH BIT,TENLOP NVARCHAR(10))
	INSERT INTO #TAM (MAHS,HOTEN,NTNS,DIACHI,DIENTHOAI,EMAIL,GIOITINH,TENLOP)
	SELECT HS.MAHS, HS.HOTEN, HS.NTNS, HS.DIACHI, HS.DIENTHOAI, HS.EMAIL, HS.GIOITINH, L.TENLOP 
	FROM HOCSINH HS,  LOP L, KHOILOP KL
	WHERE HOTEN LIKE '%' + @HOTEN + '%' 
		AND MAHS LIKE '%' + @MAHS + '%' 
		AND GIOITINH LIKE  '%' + @GIOITINH + '%'
		AND HOTEN LIKE  @HO + '%' 
		AND HOTEN LIKE N'%' + @TEN
		AND KL.MAKHOILOP LIKE '%' + @MAKHOILOP + '%'
		AND HS.MALOP LIKE '%' + @MALOP + '%'  
		AND HS.MALOP = L.MALOP
		AND L.MAKHOILOP = KL.MAKHOILOP
	IF(@MALOP = '' AND @MAKHOILOP = '')
	BEGIN
		INSERT INTO #TAM (MAHS,HOTEN,NTNS,DIACHI,DIENTHOAI,EMAIL,GIOITINH)
		SELECT  HS.MAHS,HS.HOTEN,HS.NTNS,HS.DIACHI,HS.DIENTHOAI,HS.EMAIL,HS.GIOITINH 
		FROM HOCSINH HS
		WHERE HS.HOTEN LIKE '%' + @HOTEN + '%' 
			AND HS.MAHS LIKE '%' + @MAHS + '%' 
			AND HS.GIOITINH LIKE '%' + @GIOITINH + '%'
			AND HS.HOTEN LIKE  @HO + '%' 
			AND HS.HOTEN LIKE '%' + @TEN
			AND HS.MALOP is NULL 
	END
	SELECT MAHS,HOTEN,NTNS,DIACHI,DIENTHOAI,EMAIL,GIOITINH,TENLOP FROM #TAM ORDER BY HOTEN ASC
END

--EXEC sp_TraCuuThongTinHocSinh '','','','','','KL02',''
--Tra cứu danh sách lớp theo khối lớp
IF OBJECT_ID('sp_TraCuuDanhSachLop', 'P') IS NOT NULL
	DROP PROCEDURE sp_TraCuuDanhSachLop
GO
CREATE PROCEDURE sp_TraCuuDanhSachLop (@MAKHOILOP NVARCHAR(9))
AS
BEGIN
	SELECT TENLOP, MALOP
	FROM LOP
	WHERE MAKHOILOP = @MAKHOILOP
END

--Liệt kê danh sách học sinh chưa có lớp
IF OBJECT_ID('sp_LietKeDanhSachHocSinhPhanLop', 'P') IS NOT NULL
	DROP PROCEDURE sp_LietKeDanhSachHocSinhPhanLop 
GO
CREATE PROCEDURE sp_LietKeDanhSachHocSinhPhanLop (@MALOP NVARCHAR(4))
AS
BEGIN
	IF(@MALOP = '')
	BEGIN
		SELECT MAHS,HOTEN, NTNS
		FROM HOCSINH
		WHERE MALOP IS NULL
	END
	ELSE
		SELECT MAHS,HOTEN, NTNS
		FROM HOCSINH
		WHERE MALOP = @MALOP
END

--Liệt kê danh sách lớp
IF OBJECT_ID('sp_LietKeDanhSachLop', 'P') IS NOT NULL
	DROP PROCEDURE sp_LietKeDanhSachLop
GO
CREATE PROCEDURE sp_LietKeDanhSachLop
AS
BEGIN
	CREATE TABLE #TAM (MALOP NVARCHAR(4),TENLOP NVARCHAR(10),SISO INT,SISOHIENTAI INT,FLAG INT)
	INSERT INTO #TAM (MALOP,TENLOP,SISO)
	SELECT MALOP,TENLOP,SISO 
	FROM LOP
	UPDATE #TAM SET FLAG = 0
	WHILE(SELECT COUNT(*) FROM #TAM WHERE FLAG = 0) > 0 
	BEGIN
		DECLARE @MALOP NVARCHAR(4)
		SET @MALOP = (SELECT TOP 1 MALOP FROM #TAM WHERE FLAG = 0)
		UPDATE #TAM SET SISOHIENTAI = (SELECT COUNT(*) FROM HOCSINH WHERE MALOP = @MALOP),FLAG = 1 WHERE MALOP = @MALOP
	END
	SELECT MALOP,TENLOP,SISO,SISOHIENTAI FROM #TAM
END

-- Kiểm tra sỉ số hiện tại
IF OBJECT_ID('sp_KiemTraSiSoHienTai', 'P') IS NOT NULL
	DROP PROCEDURE sp_KiemTraSiSoHienTai
GO
CREATE PROCEDURE sp_KiemTraSiSoHienTai (@MALOP NVARCHAR(4))
AS
BEGIN
	select count (*) MAHS from hocsinh where malop = @MALOP
	UNION
	select SISO from LOP where malop = @MALOP
END
-- Cập nhật lớp học sinh
IF OBJECT_ID('sp_CapNhatLop', 'P') IS NOT NULL
	DROP PROCEDURE sp_CapNhatLop
GO
CREATE PROCEDURE sp_CapNhatLop (@MAHS NVARCHAR(9),@MALOP NVARCHAR(4))
AS
BEGIN
	IF (@MALOP = '')
		UPDATE HOCSINH SET MALOP = NULL WHERE MAHS = @MAHS
	ELSE
		UPDATE HOCSINH SET MALOP = @MALOP WHERE MAHS = @MAHS
END


-- Danh Sách Học Sinh Form Quản Lý Điểm
IF OBJECT_ID('sp_DanhSachHocSinhFormQuanLyDiem', 'P') IS NOT NULL
	DROP PROCEDURE sp_DanhSachHocSinhFormQuanLyDiem
GO
CREATE PROCEDURE sp_DanhSachHocSinhFormQuanLyDiem (@MALOP NVARCHAR(4),@MAHK NVARCHAR(5),@MAMH NVARCHAR (10))
AS
BEGIN
	SELECT HS.MAHS, HS.HOTEN, D.DIEM15P, D.DIEM1T, D.DIEMTHI, D.DIEMTB, HK.TENHK, MH.TENMONHOC
	FROM HOCSINH HS,DIEM D,HOCKY HK, MONHOC MH
	WHERE HS.MAHS = D.MAHS
	AND HK.MAHK = D.MAHK
	AND MH.MAMONHOC = D.MAMONHOC
	AND HS.MALOP LIKE '%' + @MALOP + '%'
	AND MH.MAMONHOC LIKE '%' + @MAMH +'%'
	AND HK.MAHK LIKE '%'+ @MAHK +'%'
	ORDER BY HOTEN ASC
END

-- Liệt kê học kỳ
IF OBJECT_ID('sp_LietKeHocKy', 'P') IS NOT NULL
	DROP PROCEDURE sp_LietKeHocKy
GO
CREATE PROCEDURE sp_LietKeHocKy
AS
BEGIN
	SELECT * FROM HOCKY
END

--Bảng Điểm Học Sinh
IF OBJECT_ID('sp_BangDiemHocSinh', 'P') IS NOT NULL
	DROP PROCEDURE sp_BangDiemHocSinh
GO
CREATE PROCEDURE sp_BangDiemHocSinh (@MALOP NVARCHAR(4),@MAHK NVARCHAR(5),@MAMH NVARCHAR (10))
AS
BEGIN
	SELECT MAHS,HOTEN
	INTO #TEMP
	FROM HOCSINH
	WHERE MALOP = @MALOP
		
	DECLARE @MAHOCSINH NVARCHAR(9)

	WHILE (SELECT COUNT(MAHS) FROM #TEMP) > 0
	BEGIN
		SELECT TOP 1  @MAHOCSINH = MAHS FROM #TEMP
		IF NOT EXISTS (SELECT * FROM DIEM WHERE MAHS = @MAHOCSINH AND MAHK = @MAHK AND MAMONHOC = @MAMH)
		BEGIN
			INSERT INTO DIEM (DIEM15P,DIEM1T,DIEMTHI,DIEMTB,MAHS,MAMONHOC,MAHK) VALUES ('0','0','0','0',@MAHOCSINH,@MAMH,@MAHK)
		END
		DELETE FROM #TEMP WHERE MAHS = @MAHOCSINH
	END
	SELECT HS.MAHS,HS.HOTEN,MH.TENMONHOC, D.DIEM15P,D.DIEM1T,D.DIEMTHI,D.DIEMTB,L.TENLOP,HK.TENHK
	FROM HOCSINH HS,LOP L,HOCKY HK, DIEM D, MONHOC MH
	WHERE HS.MALOP = L.MALOP
		AND D.MAHS = HS.MAHS
		AND D.MAHK = HK.MAHK
		AND D.MAMONHOC = MH.MAMONHOC
		AND D.MAHK LIKE '%' + @MAHK +'%'
		AND HS.MALOP LIKE '%' +@MALOP+ '%'
		AND D.MAMONHOC LIKE '%' + @MAMH + '%'
	ORDER BY HOTEN ASC
	---DECLARE @MAHOCSINHTEST NVARCHAR(9)

	--SELECT TOP 1 @MAHOCSINHTEST = MAHS
	--FROM HOCSINH
	--WHERE MALOP = @MALOP

	--IF NOT EXISTS (SELECT * FROM DIEM WHERE MAHS = @MAHOCSINHTEST AND MAMONHOC = @MAMH AND MAHK = @MAHK)
	--BEGIN		
	--	SELECT MAHS,HOTEN
	--	INTO #TEMP
	--	FROM HOCSINH
	--	WHERE MALOP = @MALOP
	
	--	DECLARE @MAHOCSINH NVARCHAR(9)

	--	WHILE (SELECT COUNT(MAHS) FROM #TEMP) > 0
	--	BEGIN
	--		SELECT TOP 1  @MAHOCSINH = MAHS FROM #TEMP

	--		INSERT INTO DIEM (DIEM15P,DIEM1T,DIEMTHI,DIEMTB,MAHS,MAMONHOC,MAHK) VALUES ('0','0','0','0',@MAHOCSINH,@MAMH,@MAHK)

	--		DELETE FROM #TEMP WHERE MAHS = @MAHOCSINH
	--	END
	--	SELECT HS.MAHS,HS.HOTEN,MH.TENMONHOC, D.DIEM15P,D.DIEM1T,D.DIEMTHI,D.DIEMTB,L.TENLOP,HK.TENHK
	--	FROM HOCSINH HS,LOP L,HOCKY HK, DIEM D, MONHOC MH
	--	WHERE HS.MALOP = L.MALOP
	--		AND D.MAHS = HS.MAHS
	--		AND D.MAHK = HK.MAHK
	--		AND D.MAMONHOC = MH.MAMONHOC
	--		AND D.MAHK LIKE '%' + @MAHK +'%'
	--		AND HS.MALOP LIKE '%' +@MALOP+ '%'
	--		AND D.MAMONHOC LIKE '%' + @MAMH + '%'
	--	ORDER BY HOTEN ASC
		
	--END
	--ELSE
	--BEGIN
	--	SELECT HS.MAHS,HS.HOTEN,MH.TENMONHOC, D.DIEM15P,D.DIEM1T,D.DIEMTHI,D.DIEMTB,L.TENLOP,HK.TENHK
	--	FROM HOCSINH HS,LOP L,HOCKY HK, DIEM D, MONHOC MH
	--	WHERE HS.MALOP = L.MALOP
	--		AND D.MAHS = HS.MAHS
	--		AND D.MAHK = HK.MAHK
	--		AND D.MAMONHOC = MH.MAMONHOC
	--		AND D.MAHK LIKE '%' + @MAHK +'%'
	--		AND HS.MALOP LIKE '%' +@MALOP+ '%'
	--		AND D.MAMONHOC LIKE '%' + @MAMH + '%'
	--ORDER BY HOTEN ASC		
	--END
END

--Cập Nhật Điểm Học Sinh
IF OBJECT_ID('sp_CapNhatDiemHocSinh', 'P') IS NOT NULL
	DROP PROCEDURE sp_CapNhatDiemHocSinh
GO
CREATE PROCEDURE sp_CapNhatDiemHocSinh (@MAHS NVARCHAR(4),@MAHK NVARCHAR(5),@MAMH NVARCHAR (10),@DIEM15P float(53), @DIEM1T float(53), @DIEMTHI float(53), @DIEMTB float(53))
AS
BEGIN
	UPDATE DIEM
	SET DIEM15P = @DIEM15P, DIEM1T = @DIEM1T, DIEMTHI = @DIEMTHI, DIEMTB = @DIEMTB
	WHERE MAHS = @MAHS AND MAMONHOC = @MAMH AND MAHK = @MAHK
END


--Liệt kê môn học của khối lớp
IF OBJECT_ID('sp_LietKeMonHocKhoiLop', 'P') IS NOT NULL
	DROP PROCEDURE sp_LietKeMonHocKhoiLop
GO
CREATE PROCEDURE sp_LietKeMonHocKhoiLop (@MAKHOILOP NVARCHAR(5))
AS
BEGIN
	SELECT M.TENMONHOC, M.MAMONHOC
	FROM MONHOC M,KHOILOP K,MONHOC_KHOILOP MK
	WHERE M.MAMONHOC = MK.MAMH
	AND K.MAKHOILOP = MK.MAKHOILOP
	AND K.MAKHOILOP = @MAKHOILOP
END

-- Trigger Thêm mã Môn Học
IF OBJECT_ID('tr_NextID_MONHOC', 'P') IS NOT NULL
	DROP TRIGGER tr_NextID_MONHOC
GO
CREATE TRIGGER tr_NextID_MONHOC ON MONHOC
FOR INSERT
AS
BEGIN
	DECLARE @lastid CHAR(8)

	SET @lastid = (
			SELECT TOP 1 MONHOC.MAMONHOC
			FROM MONHOC
			ORDER BY MONHOC.MAMONHOC DESC
			)
	UPDATE MONHOC
	SET MONHOC.MAMONHOC = dbo.fn_NextID(@lastid, 'MH', 8)
	WHERE MONHOC.MAMONHOC = ''
END
GO

-- Thêm Môn Học
IF OBJECT_ID('sp_ThemMonHoc', 'P') IS NOT NULL
	DROP PROCEDURE sp_ThemMonHoc
GO
CREATE PROCEDURE sp_ThemMonHoc (@TENMONHOC NVARCHAR(20))
AS
BEGIN
	IF NOT EXISTS (SELECT * FROM MONHOC WHERE TENMONHOC = @TENMONHOC)
	BEGIN
		INSERT INTO MONHOC (MAMONHOC,TENMONHOC) VALUES ('',@TENMONHOC)
	END
	ELSE
	BEGIN
		RAISERROR  (N'Môn Học Đã Tồn Tại, Kiểm Tra Lại!',16,1)	
	END
END
-- Sửa tên môn học
IF OBJECT_ID('sp_SuaTenMonHoc', 'P') IS NOT NULL
	DROP PROCEDURE sp_SuaTenMonHoc
GO
CREATE PROCEDURE sp_SuaTenMonHoc (@MAMONHOC NVARCHAR(10),@TENMONHOC NVARCHAR(20))
AS
BEGIN
	IF NOT EXISTS (SELECT * FROM MONHOC WHERE TENMONHOC = @TENMONHOC)
	BEGIN
		UPDATE MONHOC
		SET TENMONHOC = @TENMONHOC
		WHERE MAMONHOC = @MAMONHOC
	END
	ELSE
	BEGIN
		RAISERROR  (N'Môn Học Đã Tồn Tại, Kiểm Tra Lại!',16,1)	
	END
END

-- Xóa môn học
IF OBJECT_ID('sp_XoaMonHoc', 'P') IS NOT NULL
	DROP PROCEDURE sp_XoaMonHoc
GO
CREATE PROCEDURE sp_XoaMonHoc (@MAMONHOC NVARCHAR(10))
AS
BEGIN
	IF EXISTS (SELECT * FROM DIEM WHERE MAMONHOC = @MAMONHOC)
	BEGIN
		RAISERROR  (N'Đã Có Điểm Cho Môn Học Này! Không Thể Xóa Môn Học!',16,1)
	END
	ELSE
	BEGIN
		DELETE FROM MONHOC_KHOILOP WHERE MAMH = @MAMONHOC
		DELETE FROM MONHOC WHERE MAMONHOC = @MAMONHOC 		
	END
END

-- Trigger Tính Điểm Trung Bình
GO
IF OBJECT_ID ('tr_TinhDiemTrungBinh', 'TR') IS NOT NULL
   DROP TRIGGER tr_TinhDiemTrungBinh;
GO
CREATE TRIGGER tr_TinhDiemTrungBinh
ON DIEM
AFTER INSERT,UPDATE
AS
BEGIN
	DECLARE @TONGDIEM FLOAT(53) = 0
	DECLARE @SOMON FLOAT(53) = 0
	DECLARE @MAMH NVARCHAR(10)
	CREATE TABLE #TAM (MAMH NVARCHAR(10),DIEMTB FLOAT(53),FLAG INT)
	IF EXISTS (SELECT * FROM INSERTED)
		BEGIN
			SELECT @SOMON = COUNT (MAMONHOC) 
			FROM MONHOC M,MONHOC_KHOILOP MK,KHOILOP K
			WHERE MK.MAKHOILOP = K.MAKHOILOP
				AND M.MAMONHOC = MK.MAMH
				AND K.MAKHOILOP = (SELECT DISTINCT MKMK.MAKHOILOP FROM INSERTED II, MONHOC MM, MONHOC_KHOILOP MKMK, HOCSINH HS, LOP LL, KHOILOP KLKL
									WHERE II.MAMONHOC = MM.MAMONHOC
									AND	MM.MAMONHOC = MKMK.MAMH
									AND HS.MAHS = II.MAHS
									AND HS.MALOP = LL.MALOP
									AND LL.MAKHOILOP = MKMK.MAKHOILOP)

			IF EXISTS (SELECT * FROM inserted WHERE MAHK = 'HK01')
			BEGIN
				SET @TONGDIEM = 0
				DELETE FROM #TAM
				INSERT INTO #TAM (MAMH,DIEMTB)
				SELECT DISTINCT D.MAMONHOC, D.DIEMTB
				FROM MONHOC_KHOILOP MK ,KHOILOP K, DIEM D, HOCSINH H, inserted I
				WHERE MK.MAKHOILOP = K.MAKHOILOP
					AND D.MAHS = H.MAHS
					AND D.MAHK LIKE I.MAHK
					AND H.MAHS LIKE I.MAHS
					AND D.MAHK LIKE I.MAHK
				UPDATE #TAM SET FLAG = 0
				WHILE(SELECT COUNT(*) FROM #TAM WHERE FLAG = 0) > 0 
				BEGIN
					SET @TONGDIEM += (SELECT TOP 1 DIEMTB FROM #TAM WHERE FLAG = 0)

					SET @MAMH = (SELECT TOP 1 MAMH FROM #TAM WHERE FLAG = 0)
					DELETE FROM #TAM WHERE MAMH = @MAMH
				END
				UPDATE HOCSINH SET TBHK1 = @TONGDIEM / @SOMON WHERE MAHS = (SELECT MAHS FROM inserted)
			END
			IF EXISTS (SELECT * FROM inserted WHERE MAHK = 'HK02')
			BEGIN
				SET @TONGDIEM = 0
				DELETE FROM #TAM
				INSERT INTO #TAM (MAMH,DIEMTB)
				SELECT DISTINCT D.MAMONHOC, D.DIEMTB
				FROM MONHOC_KHOILOP MK ,KHOILOP K, DIEM D, HOCSINH H, inserted I
				WHERE MK.MAKHOILOP = K.MAKHOILOP
					AND D.MAHS = H.MAHS
					AND D.MAHK LIKE I.MAHK
					AND H.MAHS LIKE I.MAHS
					AND D.MAHK LIKE I.MAHK
				UPDATE #TAM SET FLAG = 0
				WHILE(SELECT COUNT(*) FROM #TAM WHERE FLAG = 0) > 0 
				BEGIN
					SET @TONGDIEM += (SELECT TOP 1 DIEMTB FROM #TAM WHERE FLAG = 0)
					SET @MAMH = (SELECT TOP 1 MAMH FROM #TAM WHERE FLAG = 0)
					DELETE FROM #TAM WHERE MAMH = @MAMH
				END
				UPDATE HOCSINH SET TBHK2 = @TONGDIEM / @SOMON WHERE MAHS = (SELECT MAHS FROM inserted)
			END
		END
	ELSE IF EXISTS(SELECT * FROM INSERTED) AND EXISTS(SELECT * FROM DELETED)
	BEGIN
		SELECT @SOMON = COUNT (MAMONHOC) 
			FROM MONHOC M,MONHOC_KHOILOP MK,KHOILOP K
			WHERE MK.MAKHOILOP = K.MAKHOILOP
				AND M.MAMONHOC = MK.MAMH
				AND K.MAKHOILOP = (SELECT DISTINCT MKMK.MAKHOILOP FROM INSERTED II, MONHOC MM, MONHOC_KHOILOP MKMK, HOCSINH HS, LOP LL, KHOILOP KLKL
									WHERE II.MAMONHOC = MM.MAMONHOC
									AND	MM.MAMONHOC = MKMK.MAMH
									AND HS.MAHS = II.MAHS
									AND HS.MALOP = LL.MALOP
									AND LL.MAKHOILOP = MKMK.MAKHOILOP)
		IF EXISTS (SELECT * FROM inserted WHERE MAHK = 'HK01')
		BEGIN
			DELETE FROM #TAM
			INSERT INTO #TAM (MAMH,DIEMTB)
			SELECT DISTINCT D.MAMONHOC, D.DIEMTB
			FROM MONHOC_KHOILOP MK ,KHOILOP K, DIEM D, HOCSINH H, inserted I
			WHERE MK.MAKHOILOP = K.MAKHOILOP
				AND D.MAHS = H.MAHS
				AND D.MAHK LIKE I.MAHK
				AND H.MAHS LIKE I.MAHS
				AND D.MAHK LIKE I.MAHK
			UPDATE #TAM SET FLAG = 0
			WHILE(SELECT COUNT(*) FROM #TAM WHERE FLAG = 0) > 0 
			BEGIN
				SET @TONGDIEM += (SELECT TOP 1 DIEMTB FROM #TAM WHERE FLAG = 0)
				SET @MAMH = (SELECT TOP 1 MAMH FROM #TAM WHERE FLAG = 0)
				DELETE FROM #TAM WHERE MAMH = @MAMH
			END
			UPDATE HOCSINH SET TBHK1 = @TONGDIEM / @SOMON WHERE MAHS = (SELECT MAHS FROM inserted)
		END
		IF EXISTS (SELECT * FROM inserted WHERE MAHK = 'HK02')
		BEGIN
			SET @TONGDIEM = 0
			DELETE FROM #TAM
			INSERT INTO #TAM (MAMH,DIEMTB)
			SELECT DISTINCT D.MAMONHOC, D.DIEMTB
			FROM MONHOC_KHOILOP MK ,KHOILOP K, DIEM D, HOCSINH H, inserted I
			WHERE MK.MAKHOILOP = K.MAKHOILOP
				AND D.MAHS = H.MAHS
				AND D.MAHK LIKE I.MAHK
				AND H.MAHS LIKE I.MAHS
				AND D.MAHK LIKE I.MAHK
			UPDATE #TAM SET FLAG = 0
			WHILE(SELECT COUNT(*) FROM #TAM WHERE FLAG = 0) > 0 
			BEGIN
				SET @TONGDIEM += (SELECT TOP 1 DIEMTB FROM #TAM WHERE FLAG = 0)
				SET @MAMH = (SELECT TOP 1 MAMH FROM #TAM WHERE FLAG = 0)
				DELETE FROM #TAM WHERE MAMH = @MAMH
			END
			UPDATE HOCSINH SET TBHK2 = @TONGDIEM / @SOMON WHERE MAHS = (SELECT MAHS FROM inserted)
		END
	END
END

-- Liệt Kê Danh Sách Học Sinh Tra Cứu
IF OBJECT_ID('sp_DanhSachHocSinhTraCuu', 'P') IS NOT NULL
	DROP PROCEDURE sp_DanhSachHocSinhTraCuu
GO
CREATE PROCEDURE sp_DanhSachHocSinhTraCuu (@MALOP NVARCHAR(10),@MAKHOILOP NVARCHAR(10),@HOTEN NVARCHAR(200))
AS
BEGIN
	IF(@MAKHOILOP = '')
	BEGIN
		SELECT MAHS,HOTEN,NTNS,DIACHI,DIENTHOAI,EMAIL,GIOITINH,TENLOP
		FROM HOCSINH H, LOP L
		WHERE H.MALOP = L.MALOP
			AND H.MALOP = @MALOP
			AND H.HOTEN LIKE '%' + @HOTEN + '%'
	END
	ELSE IF(@MAKHOILOP != '')
	BEGIN
		SELECT MAHS,HOTEN,NTNS,DIACHI,DIENTHOAI,EMAIL,GIOITINH,TENLOP
		FROM HOCSINH H, LOP L,KHOILOP KL
		WHERE H.MALOP = L.MALOP
			AND L.MAKHOILOP = KL.MAKHOILOP
			AND KL.MAKHOILOP = @MAKHOILOP
			AND H.HOTEN LIKE '%' + @HOTEN + '%'	
	END
END
-- Liệt Kê Bảng Điểm Học Sinh Tra Cứu
IF OBJECT_ID('sp_BangDiemHocSinhTraCuu', 'P') IS NOT NULL
	DROP PROCEDURE sp_BangDiemHocSinhTraCuu
GO
CREATE PROCEDURE sp_BangDiemHocSinhTraCuu (@MALOP NVARCHAR(10),@MAKHOILOP NVARCHAR(10),@HOTEN NVARCHAR(200))
AS
BEGIN
	IF(@MAKHOILOP = '')
	BEGIN
		SELECT MAHS,HOTEN,TENLOP,TBHK1,TBHK2
		FROM HOCSINH H, LOP L
		WHERE H.MALOP = L.MALOP
			AND H.MALOP = @MALOP
			AND H.HOTEN LIKE '%' + @HOTEN + '%'
	END
	ELSE IF(@MAKHOILOP != '')
	BEGIN
		SELECT MAHS,HOTEN,TENLOP,TBHK1,TBHK2
		FROM HOCSINH H, LOP L,KHOILOP KL
		WHERE H.MALOP = L.MALOP
			AND L.MAKHOILOP = KL.MAKHOILOP
			AND KL.MAKHOILOP = @MAKHOILOP
			AND H.HOTEN LIKE '%' + @HOTEN + '%'	
	END
END